package airline;

import java.sql.*;
import java.util.Date;

public class Passenger {
	
	private int flightID;
	private Date date;
	private int ssn;
	private String passName;
	private int age; 
	private int reservStat;
	private int flightClass;
	private int cost;
	private String street;
	private String aptNum;
	private String city;
	private String state;
	private String email;
	private int phoneNum;

	
	/**
	 * Creates a passenger and insets the info into the database
	 * 
	 * @param numbers - an array that contains all the integer values
	 * @param strings - an array that contains all the string values
	 * @param bookdate - the date the passenger booked the flight
	 */
	public Passenger(int [] numbers, String [] strings, Date bookdate){
		// load jdbc driver
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}
		catch(ClassNotFoundException ex) {
			ex.printStackTrace(System.err);
			System.exit(1);
		}

		// Establish the connection
		String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, "bmill1", "passwd");
			System.out.println("\nConnected to Oracle.");
			System.out.println("---------------");
		}
		catch (SQLException se ){
			System.out.println("Unable to connect to Oracle.");
			se.printStackTrace();
			System.exit(1);
		}
		
		this.date = bookdate;
	
		this.ssn = numbers[0];
		this.flightID = numbers[1];
		this.age = numbers[2];
		this.reservStat = numbers[3];
		this.flightClass = numbers[4];
		this.cost = numbers[5];
		this.phoneNum = numbers[6];
		
		this.passName = strings[0];
		this.street = strings[1];
		this.aptNum = strings[2];
		this.city = strings[3];
		this.state = strings[4];
		this.email = strings[5];
		
		// Inserts passenger info into database
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate("INSERT INTO PASSENGER(ssn, flightId, bookDate, name, age, resStatus, flightClass, amountCharged)" +
			" VALUES('" + ssn + "', '" + flightID + "', '" + date + "', '" + passName + "', '" + age + "', '" + reservStat + "', '" + flightClass + "', '" + cost + "')");
			stmt.executeUpdate("INSERT INTO ADDRESS VALUES('" + ssn +"', '" + street + "', '" + aptNum + "', '" + city + "', '" + state + "', '" + phoneNum + "', '" + email + "')");
			stmt.close();
		}
		catch (SQLException ex) {
			System.out.println("Error while executing query.");
			ex.printStackTrace(System.err);
			System.exit(1);
		}
		
	}
	
	
	/**
	 * creates a passenger based off ssn so user can call functions
	 * 
	 * @param social - passenger ssn
	 */
	public Passenger(int social){
		this.ssn = social;
	}
	
	/**
	 * Function that checks a passenger into a flight
	 * Calls functions to set number of bags, assign seat number and change 
	 * reservation status to checked in (2)
	 *  
	 * @param bags - num bags
	 * @param seat - assigned seat number
	 */
	public void checkIn(int bags, int seat) {
		setBags(bags);
		setSeatNum(seat);
		changeResStatus(2);
		
	}
	
   
   /**
	 * Delete all the flight info for a passenger that cancels a ticket and returns the flight ID num
	 * 
	 * @return - an int, flight ID num
	 */
	public int cancelTicket() {
		
		// load jdbc driver
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}
		catch(ClassNotFoundException ex) {
			ex.printStackTrace(System.err);
			System.exit(1);
		}

		// Establish the connection
		String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, "bmill1", "bmill1");
			System.out.println("\nConnected to Oracle.");
			System.out.println("---------------");
		}
		catch (SQLException se ){
			System.out.println("Unable to connect to Oracle.");
			se.printStackTrace();
			System.exit(1);
		}
		
		//Delete the row containing the passengers info
		try {
			Statement stmt = con.createStatement();
			ResultSet result = stmt.executeQuery("SELECT flightId FROM PASSENGER WHERE ssn ='" + this.ssn + "'");
			this.flightID = result.getInt("flightId");
			stmt.executeUpdate("DELETE FROM PASSENGER WHERE ssn ='" + this.ssn + "'");
			stmt.close();
		}
		catch (SQLException ex) {
			System.out.println("Error while executing query.");
			ex.printStackTrace(System.err);
			System.exit(1);
		}
		return this.flightID;
	}
	
	
	/**
	 * gets the flight Id of the passengers flight
	 * 
	 * @return - an int, the flight ID
	 */
	public int getFlightID() {
		// load jdbc driver
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}
		catch(ClassNotFoundException ex) {
			ex.printStackTrace(System.err);
			System.exit(1);
		}

		// Establish the connection
		String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, "bmill1", "bmill1");
			System.out.println("\nConnected to Oracle.");
			System.out.println("---------------");
		}
		catch (SQLException se ){
			System.out.println("Unable to connect to Oracle.");
			se.printStackTrace();
			System.exit(1);
		}
		
		//Gets the flight ID for the passengers flight
		try {
			Statement stmt = con.createStatement();
			
			ResultSet result = stmt.executeQuery("SELECT flightId FROM PASSENGER WHERE ssn ='" + this.ssn + "'");
			this.flightID = result.getInt("flightId");
			stmt.close();
		}
		catch (SQLException ex) {
			System.out.println("Error while executing query.");
			ex.printStackTrace(System.err);
			System.exit(1);
		}
		return this.flightID;
	}
   
   
	/**
	 * change the status of the passenger
	 * 
	 * @param status - 0 for waiting, 1 for confirmed, 2 for checked in
	 */
	private void changeResStatus(int status) {
		
		// load jdbc driver
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}
		catch(ClassNotFoundException ex) {
			ex.printStackTrace(System.err);
			System.exit(1);
		}

		// Establish the connection
		String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, "bmill1", "passwd");
			System.out.println("\nConnected to Oracle.");
			System.out.println("---------------");
		}
		catch (SQLException se ){
			System.out.println("Unable to connect to Oracle.");
			se.printStackTrace();
			System.exit(1);
		}
		
		//Update the reservation status
		try {
			Statement stmt = con.createStatement();
			
			stmt.executeUpdate("UPDATE PASSENGER SET resStatus='" + status + "' WHERE ssn ='" + this.ssn + "'");
			stmt.close();
		}
		catch (SQLException ex) {
			System.out.println("Error while executing query.");
			ex.printStackTrace(System.err);
			System.exit(1);
		}
		
	}
	
	
	/**
	 * Sets the number of bags for the passenger
	 * 
	 * @param bags - number of bags
	 */
	private void setBags(int bags) {
		// load jdbc driver
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}
		catch(ClassNotFoundException ex) {
			ex.printStackTrace(System.err);
			System.exit(1);
		}

		// Establish the connection
		String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, "bmill1", "passwd");
			System.out.println("\nConnected to Oracle.");
			System.out.println("---------------");
		}
		catch (SQLException se ){
			System.out.println("Unable to connect to Oracle.");
			se.printStackTrace();
			System.exit(1);
		}
		
		try {
			Statement stmt = con.createStatement();
			
			stmt.executeUpdate("UPDATE PASSENGER SET numBags='" + bags + "' WHERE ssn='" + this.ssn + "'");
			stmt.close();
		}
		catch (SQLException ex) {
			System.out.println("Error while executing query.");
			ex.printStackTrace(System.err);
			System.exit(1);
		}
		
		
	}
	
	
	/**
	 * sets the passengers assigned seat number
	 * 
	 * @param seatNum - assigned seat number
	 */
	private void setSeatNum(int seatNum) {
		
		// load jdbc driver
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}
		catch(ClassNotFoundException ex) {
			ex.printStackTrace(System.err);
			System.exit(1);
		}

		// Establish the connection
		String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, "bmill1", "passwd");
			System.out.println("\nConnected to Oracle.");
			System.out.println("---------------");
		}
		catch (SQLException se ){
			System.out.println("Unable to connect to Oracle.");
			se.printStackTrace();
			System.exit(1);
		}
		
		//Updates the seat number
		try {
			Statement stmt = con.createStatement();
			
			stmt.executeUpdate("UPDATE PASSENGER SET seatNum='" + seatNum + "' WHERE ssn='" + this.ssn + "'");
			stmt.close();
		}
		catch (SQLException ex) {
			System.out.println("Error while executing query.");
			ex.printStackTrace(System.err);
			System.exit(1);
		}
	}


}
